#!/usr/bin/python
# -- coding:utf8 --

# 线上部署代码
import os
import sys
sys.path.append(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
import app.model_phl.util_log as utils
from app.model_phl.utils import select_sql_mysql
import app.model_phl.my_tool_package as my_package

import time
import pandas as pd
import numpy as np
import json
import re
import warnings

warnings.filterwarnings("ignore")


file_path_name1 = './model/sms_type_dict_ph_v1.json'
current_path = os.path.dirname(__file__)

file_path_name1 = file_path_name1.replace("./", current_path + "/")
with open(file_path_name1, 'r') as f:
    message_type_dict = json.loads(f.read())

def get_condition_df(df, condition_field, match_list='none'):
    """
    找到满足content属于某一类别的数据,精确匹配
    :param df:
    :param condition_field:
    :param match_list:
    :return:
    """
    if df.empty:
        return pd.DataFrame()
    df_tmp = df.copy().reset_index(drop=True)
    # 找到满足时间窗口的数据
    if match_list == 'none':
        return df_tmp
    else:
        return df_tmp[df_tmp[condition_field].isin(match_list)]


def get_list_value_is_in_str(match_list, params_str):
    """
    检查文本信息是否再匹配列表里面
    :param match_list:
    :param params_str:
    :return:
    """
    for v in match_list:
        if type(v) == list:
            flg = 1
            for v_ in v:
                if str(v_).strip().lower() not in str(params_str).strip().lower():
                    flg = 0
            if flg == 1:
                return 1
        else:
            if str(v).strip().lower() in str(params_str).strip().lower():
                return 1
    return 0


def get_which_value_in_list(match_list, params_str):
    for v in match_list:
        if type(v) == list:
            flg = 1
            for v_ in v:
                if str(v_).strip().lower() not in str(params_str).strip().lower():
                    flg = 0
            if flg == 1:
                return str(v_).strip().lower()
        else:
            if str(v).strip().lower() in str(params_str).strip().lower():
                return str(v).strip().lower()
    return 'None'


def get_condition_fuzzy_df(df, condition_field, match_list='none'):
    """
     找到满足content属于某一类别的数据,模糊匹配
    :param df:
    :param condition_field:
    :param match_list:
    :return:
    """
    if df.empty:
        return pd.DataFrame()
    df_tmp = df.copy().reset_index(drop=True)
    # 找到满足时间窗口的数据
    if match_list == 'none':
        return df_tmp
    else:
        return df_tmp[df_tmp[condition_field].apply(lambda x: get_list_value_is_in_str(match_list, x) == 1)]


def get_label_content(x, key_dict, cal_list):
    """
    给文本信息打上标签信息
    :param x:
    :param key_dict:
    :param cal_list:
    :return:
    """
    label_list = ['All']
    for key in cal_list:
        if key == 'All':
            continue
        else:
            match_list = key_dict[key]
            if get_list_value_is_in_str(match_list, x) == 1:
                label_list.append(key)
    return list(set(label_list))


def get_windows_aggregation_main(df, con, dt, con_key, cal_field, cal_type, condition_dict_all, dt_dict):
    """
    统计聚合函数
    :param df:
    :param con:
    :param dt:
    :param con_key:
    :param cal_field:
    :param cal_type:
    :param condition_dict_all:
    :param dt_dict:
    :return:
    """
    df_tmp = df.copy()
    df_tmp[cal_field] = df_tmp[cal_field].apply(lambda x: np.nan if (my_package.get_is_none(x) == True) else x)
    default_list = [cal_field]
    df_time_tmp = df_tmp[dt_dict[dt]]
    if df_time_tmp.empty:
        df_time_tmp = pd.DataFrame(columns=default_list)
    df_con_tmp = df_tmp[condition_dict_all[con] & dt_dict[dt] & condition_dict_all[con_key]]
    df_con_tmp = df_con_tmp[pd.isnull(df_con_tmp[cal_field]) == False]
    if df_con_tmp.empty:
        df_con_tmp = pd.DataFrame(columns=default_list)
    if cal_type == 'Cnt':
        re_value = df_con_tmp[cal_field].count()
        if df_con_tmp.empty:
            re_value = -999999
    elif cal_type == 'DistinctCnt':
        re_value = df_con_tmp[cal_field].nunique()
        if df_con_tmp.empty:
            re_value = -999999
    elif cal_type == 'RepeatCnt':
        unique_cnt = df_con_tmp[cal_field].nunique()
        _cnt = df_con_tmp[cal_field].count()
        if df_con_tmp.empty:
            re_value = -999999
        else:
            re_value = _cnt - unique_cnt
    elif cal_type == 'Std':
        if df_con_tmp.empty:
            re_value = -999999
        else:
            re_value = round(df_con_tmp[cal_field].std(), 2)
    elif cal_type == 'Avg':
        if df_con_tmp.empty:
            re_value = -999999
        else:
            re_value = round(df_con_tmp[cal_field].mean(), 2)
    elif cal_type == 'Max':
        if df_con_tmp.empty:
            re_value = -999999
        else:
            re_value = round(df_con_tmp[cal_field].max(), 2)
    elif cal_type == 'Min':
        if df_con_tmp.empty:
            re_value = -999999
        else:
            re_value = round(df_con_tmp[cal_field].min(), 2)
    elif cal_type == 'Sum':
        if df_con_tmp.empty:
            re_value = -999999
        else:
            re_value = round(df_con_tmp[cal_field].sum(), 2)
    elif cal_type == 'Pct':
        df_dem_tmp = df_tmp[condition_dict_all[con] & dt_dict[dt]]
        dem = df_dem_tmp[cal_field].count()
        num = df_con_tmp[cal_field].count()
        if df_dem_tmp.empty:
            dem = -999999
        if df_con_tmp.empty:
            num = -999999
        if df_dem_tmp.empty and df_con_tmp.empty:
            re_value = -999999
        else:
            re_value = my_package.get_cal_division(num, dem)
    elif cal_type == 'PctV2':
        df_dem_tmp = df_tmp[condition_dict_all[con_key] & dt_dict[dt]]
        dem = df_dem_tmp[cal_field].count()
        num = df_con_tmp[cal_field].count()
        if df_dem_tmp.empty:
            dem = -999999
        if df_con_tmp.empty:
            num = -999999
        if df_dem_tmp.empty and df_con_tmp.empty:
            re_value = -999999
        else:
            re_value = my_package.get_cal_division(num, dem)
    elif cal_type == 'DistinctPct':
        dem = df_con_tmp[cal_field].count()
        num = df_con_tmp[cal_field].nunique()
        if df_con_tmp.empty:
            re_value = -999999
        else:
            re_value = my_package.get_cal_division(num, dem)
    elif cal_type == 'NuniquePct':
        df_dem_tmp = df_tmp[condition_dict_all[con] & dt_dict[dt]]
        dem = df_dem_tmp[cal_field].nunique()
        num = df_con_tmp[cal_field].nunique()
        if df_dem_tmp.empty:
            dem = -999999
        if df_con_tmp.empty:
            num = -999999
        if df_dem_tmp.empty and df_con_tmp.empty:
            re_value = -999999
        else:
            re_value = my_package.get_cal_division(num, dem)
    elif cal_type == 'NuniquePctV2':
        df_dem_tmp = df_tmp[condition_dict_all[con_key] & dt_dict[dt]]
        dem = df_dem_tmp[cal_field].nunique()
        num = df_con_tmp[cal_field].nunique()
        if df_dem_tmp.empty:
            dem = -999999
        if df_con_tmp.empty:
            num = -999999
        if df_dem_tmp.empty and df_con_tmp.empty:
            re_value = -999999
        else:
            re_value = my_package.get_cal_division(num, dem)
    elif cal_type == 'RepeatPct':
        cnt_dem = df_con_tmp[cal_field].count()
        unique_cnt_num = df_con_tmp[cal_field].nunique()
        num = cnt_dem - unique_cnt_num
        if df_con_tmp.empty:
            re_value = -999999
        else:
            re_value = my_package.get_cal_division(num, cnt_dem)
    elif cal_type == 'Median':
        if df_con_tmp.empty:
            re_value = -999999
        else:
            re_value = round(df_con_tmp[cal_field].median(), 2)
    elif cal_type == 'Skew':
        if df_con_tmp.empty:
            re_value = -999999
        else:
            re_value = round(df_con_tmp[cal_field].skew(), 2)
    elif cal_type == 'Mad':
        if df_con_tmp.empty:
            re_value = -999999
        else:
            re_value = round(df_con_tmp[cal_field].mad(), 2)
    else:
        re_value = -999999
    if str(re_value).lower().strip() in ['nan']:
        re_value = -999999
    return re_value


def get_which_match_key_words(x, keywords_list):
    """
    匹配关键词方法，返回匹配的关键词，循环的方式，若匹配到列表中的某一关键词，则返回匹配的关键词
    :param x:
    :param keywords_list:
    :return:
    """
    for keyword in keywords_list:
        if type(keyword) == list:
            flg = 1
            for keyword_ in keyword:
                if str(keyword_).strip().lower() not in str(x).strip().lower():
                    flg = 0
            if flg == 1:
                return keyword
        if str(keyword).strip().lower() in str(x).strip().lower():
            keywords = str(keyword).strip().lower()
            matchObj = re.match(r'[(\[]+.*?{}.*?[)\]]+'.format(keywords), str(x), re.M | re.I)
            if matchObj:
                if len(str(matchObj.group()).strip().lower().split(' ')) > 3:
                    continue
                return (re.sub(r'[()\[\]\s{}]', '', str(matchObj.group()).strip().lower()))
            else:
                return keyword
    else:
        return 'none'


def decode_str(x):
    """
    数据解析函数
    :param x:
    :return:
    """
    arr = x.split(',')
    content = "";
    if len(arr) > 0:
        try:
            for v in arr:
                vDecode = chr(int(v, 16))
                content = content + vDecode
        except:
            content = content + v
    else:
        content = ""
    return content


def get_sms_data(phone_sub, ApplyNO):
    """
    从MySQL获取短信原始数据集
    :param phone_sub:
    :param ApplyNO:
    :return:
    """
    # 获取sms数据的SQL逻辑
    sql_str = f"""
    select 
    Body as Content
    ,Address as Phone
    ,Date as Time
    ,CreateDate as SMS_CREATE_TIME
    ,TIMESTAMPDIFF(SECOND,Date,CreateDate) as TimeInterval
    ,SUBSTRING(Date,1,10) as MessageDays
    ,ThisApplyNO as ApplyNO
    from fk_smslog_{phone_sub} 
    where ThisApplyNO = '{ApplyNO}'
    """
    # 获取SMS数据集
    sms_data = select_sql_mysql(sql_str)
    return sms_data


def extract_balance(text):
    """
    sms文本获取balance数据
    :param text:
    :return:
    """
    pattern = r'(?<=balance is )\b[Pp]\d+(?:\.\d+)?\b'
    match = re.search(pattern, text)
    balance = float(match.group().replace('P', '').replace('p', '')) if match else 0
    return balance


def get_format_data_v1(sms_data):
    """
    短信数据格式化
    :param sms_data:
    :return:
    """
    try:
        if sms_data.empty:
            return pd.DataFrame()
        sms_data.loc[:, 'Content1'] = sms_data['Content'].apply(lambda x: decode_str(x))
        sms_data.rename(columns={'Content1': 'Msg'}, inplace=True)
        sms_data.loc[:, 'Balance'] = sms_data['Msg'].apply(lambda x: extract_balance(x))
        msg_field_list = ['All', 'LoanFuzzyApp', 'Relatives', 'RepayRemind', 'Bank', 'Loan', 'Cash', 'LoanSuccess', 'RepaySuccess']
        sms_data.loc[:, 'conLabel'] = sms_data["Msg"].apply(
            lambda x: get_label_content(x, message_type_dict, msg_field_list))

        cal_field_list = ['Msg', 'Balance', 'TimeInterval',
                          'MessageDays']
        for cal_field in cal_field_list:
            sms_data[cal_field] = sms_data[cal_field].apply(
                lambda x: np.nan if my_package.get_is_none(x) == True else x)
        return sms_data
    except Exception as e:
        utils.get_logger().error(e)
        return pd.DataFrame()

def get_free_sms_features(phone_sub, ApplyNO):
    """
    获取sms数据集和计算sms特征集
    :param phone_sub:
    :param ApplyNO:
    :return:
    """
    sms_data = get_sms_data(phone_sub, ApplyNO)
    sms_data = calculate_sms_features(sms_data)
    return sms_data


def calculate_sms_features(sms_data):
    """
    获取sms数据集和计算sms特征集
    :param phone_sub:
    :param ApplyNO:
    :return:
    """
    try:
        if sms_data.empty:
            return {
                'smsCntAllD': -999999,

                # 老客评分V1版本
                'smsAllLoanFuzzyAppMsgNunique3D': -999999,
                'smsAllRelativesMsgNunique7D': -999999,
                'smsAllRepayRemindTimeIntervalMedianAllD': -999999,
                'smsAllRelativesMessageDaysRepeatCnt1D': -999999,
                'smsAllLoanFuzzyAppMsgCnt3D': -999999,
                'smsAllBankTimeIntervalAvg60D': -999999,
                'smsAllRelativesTimeIntervalMedian30D': -999999,
                'smsAllLoanTimeIntervalMedianAllD': -999999,
                'smsAllRepayRemindTimeIntervalMedian14D': -999999,
                'smsAllCashTimeIntervalMax60D': -999999,
                'smsAllLoanSuccessTimeIntervalMin3D': -999999,
                'smsAllLoanFuzzyAppMsgNunique7D': -999999,
                'smsAllCashMessageDaysRepeatCnt3D': -999999,
                'smsAllRelativesMsgRepeatCntAllD': -999999,
                'smsAllRelativesTimeIntervalMin3D': -999999,
                'smsAllRelativesMessageDaysRepeatCnt3D': -999999,
                'smsAllCashMsgNunique3D': -999999,
                'smsAllRepayRemindTimeIntervalMin3D': -999999,
                'smsAllLoanFuzzyAppBalanceSum7D': -999999,

                # 业务侧反馈用于计算短信的逾期条数的变量
                'overdue_sms_cnt': -999999,

            }

        df2 = get_format_data_v1(sms_data)
        df_tmp1 = df2.copy()
        # 筛选符合条件的数据
        con_field_list = ['All', 'LoanFuzzyApp', 'Relatives', 'RepayRemind', 'Bank', 'Loan', 'Cash',
                          'LoanSuccess', 'RepaySuccess']
        condition_dict_all = {}
        df_tmp1['All'] = 1
        for con in con_field_list:
            if con in ['All']:
                index = df_tmp1.All.ge(1)
            else:
                index = df_tmp1.conLabel.apply(lambda x: con in x)
            condition_dict_all[con] = index

        # 时间条件
        dt_dict = {}
        dt_list = ['All', 1, 3, 7, 14, 30, 60]
        for dt in dt_list:
            if dt in ['All']:
                index = df_tmp1.All.ge(1)
            else:
                index = df_tmp1.TimeInterval.isin(range(0, dt * 24 * 3600))
            dt_dict[dt] = index

        # 短信条数统计
        smsCntAllD = get_windows_aggregation_main(df_tmp1, 'All', 'All', 'All', 'Msg', 'Cnt', condition_dict_all,
                                                  dt_dict)

        # 老客评分V1模型
        smsAllLoanFuzzyAppMsgNunique3D = get_windows_aggregation_main(df_tmp1, 'All', 3, 'LoanFuzzyApp', 'Msg', 'DistinctCnt', condition_dict_all,
                                                  dt_dict)
        smsAllRelativesMsgNunique7D = get_windows_aggregation_main(df_tmp1, 'All', 7, 'Relatives', 'Msg', 'DistinctCnt', condition_dict_all,
                                                  dt_dict)
        smsAllRepayRemindTimeIntervalMedianAllD = get_windows_aggregation_main(df_tmp1, 'All', 'All', 'RepayRemind', 'TimeInterval', 'Median', condition_dict_all,
                                                  dt_dict)
        smsAllRelativesMessageDaysRepeatCnt1D = get_windows_aggregation_main(df_tmp1, 'All', 1, 'Relatives', 'MessageDays', 'RepeatCnt', condition_dict_all,
                                                  dt_dict)
        smsAllLoanFuzzyAppMsgCnt3D = get_windows_aggregation_main(df_tmp1, 'All', 3, 'LoanFuzzyApp', 'Msg', 'Cnt', condition_dict_all,
                                                  dt_dict)
        smsAllBankTimeIntervalAvg60D = get_windows_aggregation_main(df_tmp1, 'All', 60, 'Bank', 'TimeInterval', 'Avg', condition_dict_all,
                                                  dt_dict)
        smsAllRelativesTimeIntervalMedian30D =  get_windows_aggregation_main(df_tmp1, 'All', 30, 'Relatives', 'TimeInterval', 'Median', condition_dict_all,
                                                  dt_dict)
        smsAllLoanTimeIntervalMedianAllD = get_windows_aggregation_main(df_tmp1, 'All', 'All', 'Loan', 'TimeInterval', 'Median', condition_dict_all,
                                                  dt_dict)
        smsAllRepayRemindTimeIntervalMedian14D =  get_windows_aggregation_main(df_tmp1, 'All', 14, 'RepayRemind', 'TimeInterval', 'Median', condition_dict_all,
                                                  dt_dict)
        smsAllCashTimeIntervalMax60D =  get_windows_aggregation_main(df_tmp1, 'All', 60, 'Cash', 'TimeInterval', 'Max', condition_dict_all,
                                                  dt_dict)
        smsAllLoanSuccessTimeIntervalMin3D =  get_windows_aggregation_main(df_tmp1, 'All', 3, 'LoanSuccess', 'TimeInterval', 'Min', condition_dict_all,
                                                  dt_dict)
        smsAllLoanFuzzyAppMsgNunique7D = get_windows_aggregation_main(df_tmp1, 'All', 7, 'LoanFuzzyApp', 'Msg', 'DistinctCnt', condition_dict_all,
                                                  dt_dict)
        smsAllCashMessageDaysRepeatCnt3D = get_windows_aggregation_main(df_tmp1, 'All', 3, 'Cash', 'MessageDays', 'RepeatCnt', condition_dict_all,
                                                  dt_dict)
        smsAllRelativesMsgRepeatCntAllD = get_windows_aggregation_main(df_tmp1, 'All', 'All', 'Relatives', 'Msg', 'RepeatCnt', condition_dict_all,
                                                  dt_dict)
        smsAllRelativesTimeIntervalMin3D =  get_windows_aggregation_main(df_tmp1, 'All', 3, 'Relatives', 'TimeInterval', 'Min', condition_dict_all,
                                                  dt_dict)
        smsAllRelativesMessageDaysRepeatCnt3D = get_windows_aggregation_main(df_tmp1, 'All', 3, 'Relatives', 'MessageDays', 'RepeatCnt', condition_dict_all,
                                                  dt_dict)
        smsAllCashMsgNunique3D = get_windows_aggregation_main(df_tmp1, 'All', 3, 'Cash', 'Msg', 'DistinctCnt', condition_dict_all,
                                                  dt_dict)
        smsAllRepayRemindTimeIntervalMin3D =  get_windows_aggregation_main(df_tmp1, 'All', 3, 'RepayRemind', 'TimeInterval', 'Min', condition_dict_all,
                                                  dt_dict)
        smsAllLoanFuzzyAppBalanceSum7D = get_windows_aggregation_main(df_tmp1, 'All', 7, 'LoanFuzzyApp', 'Balance', 'Sum', condition_dict_all,
                                                  dt_dict)

        try:
            sms_df_2 = df2.copy()
            sms_df_2 = sms_df_2[sms_df_2['Content'].str.contains('overdue')]
            overdue_sms_cnt = len(sms_df_2.drop_duplicates(subset=['Content']))
        except:
            overdue_sms_cnt = None

        return {
                'smsCntAllD': smsCntAllD,

                # 老客评分V1版本
                'smsAllLoanFuzzyAppMsgNunique3D': smsAllLoanFuzzyAppMsgNunique3D,
                'smsAllRelativesMsgNunique7D': smsAllRelativesMsgNunique7D,
                'smsAllRepayRemindTimeIntervalMedianAllD': smsAllRepayRemindTimeIntervalMedianAllD,
                'smsAllRelativesMessageDaysRepeatCnt1D': smsAllRelativesMessageDaysRepeatCnt1D,
                'smsAllLoanFuzzyAppMsgCnt3D': smsAllLoanFuzzyAppMsgCnt3D,
                'smsAllBankTimeIntervalAvg60D': smsAllBankTimeIntervalAvg60D,
                'smsAllRelativesTimeIntervalMedian30D': smsAllRelativesTimeIntervalMedian30D,
                'smsAllLoanTimeIntervalMedianAllD': smsAllLoanTimeIntervalMedianAllD,
                'smsAllRepayRemindTimeIntervalMedian14D': smsAllRepayRemindTimeIntervalMedian14D,
                'smsAllCashTimeIntervalMax60D': smsAllCashTimeIntervalMax60D,
                'smsAllLoanSuccessTimeIntervalMin3D': smsAllLoanSuccessTimeIntervalMin3D,
                'smsAllLoanFuzzyAppMsgNunique7D': smsAllLoanFuzzyAppMsgNunique7D,
                'smsAllCashMessageDaysRepeatCnt3D': smsAllCashMessageDaysRepeatCnt3D,
                'smsAllRelativesMsgRepeatCntAllD': smsAllRelativesMsgRepeatCntAllD,
                'smsAllRelativesTimeIntervalMin3D': smsAllRelativesTimeIntervalMin3D,
                'smsAllRelativesMessageDaysRepeatCnt3D': smsAllRelativesMessageDaysRepeatCnt3D,
                'smsAllCashMsgNunique3D': smsAllCashMsgNunique3D,
                'smsAllRepayRemindTimeIntervalMin3D': smsAllRepayRemindTimeIntervalMin3D,
                'smsAllLoanFuzzyAppBalanceSum7D': smsAllLoanFuzzyAppBalanceSum7D,

                # 业务侧反馈用于决策逾期短信条数
                'overdue_sms_cnt': overdue_sms_cnt,

        }
    except Exception as e:
        utils.get_logger().error(e)
        return {
                # 统计短信的条数
                'smsCntAllD': -999976,

                # 老客评分V1版本
                'smsAllLoanFuzzyAppMsgNunique3D': -999976,
                'smsAllRelativesMsgNunique7D': -999976,
                'smsAllRepayRemindTimeIntervalMedianAllD': -999976,
                'smsAllRelativesMessageDaysRepeatCnt1D': -999976,
                'smsAllLoanFuzzyAppMsgCnt3D': -999976,
                'smsAllBankTimeIntervalAvg60D': -999976,
                'smsAllRelativesTimeIntervalMedian30D': -999976,
                'smsAllLoanTimeIntervalMedianAllD': -999976,
                'smsAllRepayRemindTimeIntervalMedian14D': -999976,
                'smsAllCashTimeIntervalMax60D': -999976,
                'smsAllLoanSuccessTimeIntervalMin3D': -999976,
                'smsAllLoanFuzzyAppMsgNunique7D': -999976,
                'smsAllCashMessageDaysRepeatCnt3D': -999976,
                'smsAllRelativesMsgRepeatCntAllD': -999976,
                'smsAllRelativesTimeIntervalMin3D': -999976,
                'smsAllRelativesMessageDaysRepeatCnt3D': -999976,
                'smsAllCashMsgNunique3D': -999976,
                'smsAllRepayRemindTimeIntervalMin3D': -999976,
                'smsAllLoanFuzzyAppBalanceSum7D': -999976,

                'overdue_sms_cnt': -999976,

        }


if __name__ == '__main__':
    # 测试用例
    phone_sub = 98
    ApplyNO = '169736809782272554'
    sms_features = get_free_sms_features(phone_sub, ApplyNO)
    print(sms_features)

